package ext.tianma.bom.service.sourcingInterface;

import ext.tianma.bom.bean.ECOBean;
import ext.tianma.bom.util.IBAUtil;
import wt.method.MethodContext;
import wt.pom.Transaction;
import wt.pom.WTConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @description: Sourcing 调用PLM接口，将品牌相关数据传给PLM
 * @author: MaggieHao
 * @createDate: 2019/8/12 10:23
 * @updateRemark: 修改内容
 * @version: 1.0
 */
public class SourcingForPLMDao {

    /**
     * 保存Souring 传过来的品牌数据
     *
     * @param
     * @return
     */
    public static SourcingForPLMReturn saveBrand(List<SourcingForPLMBean> sourcingForPLMBeanList) {
        //获取所有的BrandId
        List<String> brandIdList = getBrandIdList(sourcingForPLMBeanList);
        String msg = "此次保存的品牌数据共：" + sourcingForPLMBeanList.size() + "条。";

        SourcingForPLMReturn sourcingForPLMReturn = new SourcingForPLMReturn();

        sourcingForPLMReturn.setBrandIdList(brandIdList);;
        WTConnection conn = null;
        Transaction trans = new Transaction();
        try {
            System.out.println("开始执行插入数据~");

            MethodContext methodcontext = MethodContext.getContext();
            conn = (WTConnection) methodcontext.getConnection();
            //批量创建品牌信息，增加事务控制，如果部分失败全部回滚
            trans.start();
            batchCreate(sourcingForPLMBeanList, conn);
            trans.commit();

            System.out.println("Sourcing for PLM 插入数据完成！");
            sourcingForPLMReturn.setMsg(msg);
            sourcingForPLMReturn.setStatus("success");
            sourcingForPLMReturn.setBrandIdList(brandIdList);
            return sourcingForPLMReturn;
        } catch (Exception e) {
            e.printStackTrace();
            trans.rollback();//发生报错回滚
            msg += e.getMessage();
            sourcingForPLMReturn.setMsg(msg);
            sourcingForPLMReturn.setStatus("failed");
            return sourcingForPLMReturn;
        } finally {//关闭
            if (conn != null) {
                conn.release();
            }
        }

    }


    /**
     * 批量保存 品牌信息
     * 如果数据库中存在 则更新，没有则创建
     *
     * @param sourcingForPLMBeanList
     * @param conn
     * @throws SQLException
     */
    private static void batchCreate(List<SourcingForPLMBean> sourcingForPLMBeanList, WTConnection conn) throws SQLException {
        PreparedStatement preparedStatement;
        StringBuilder sql = new StringBuilder();

        sql.append("MERGE INTO TM_SOURCINGFORPLM t USING dual ON (t.BRANDID =? )");

        sql.append(" WHEN MATCHED THEN UPDATE SET CHINESENAME = ?,ENGLISHNAME = ?,ABBREVIATION = ?,BRANDSTATE = ?,FIRSTGRADECATEGORY = ?,MULTILEVELMATERIALCATEGORIES = ?");
        sql.append(" WHEN NOT MATCHED THEN INSERT( BRANDID,CHINESENAME,ENGLISHNAME,ABBREVIATION,BRANDSTATE,FIRSTGRADECATEGORY,MULTILEVELMATERIALCATEGORIES) ");
        sql.append(" values (?,?,?,?,?,?,?) ");
        System.out.println("sql:" + sql);
        preparedStatement = conn.prepareStatement(sql.toString());
        for (SourcingForPLMBean sourcingForPLMBean : sourcingForPLMBeanList) {
            preparedStatement.setString(1, sourcingForPLMBean.getBrandId());

            preparedStatement.setString(2, sourcingForPLMBean.getChineseName());
            preparedStatement.setString(3, sourcingForPLMBean.getEnglishName());
            preparedStatement.setString(4, sourcingForPLMBean.getAbbreviation());
            preparedStatement.setString(5, sourcingForPLMBean.getBrandState());
            preparedStatement.setString(6, sourcingForPLMBean.getFirstGradeCategory());
            preparedStatement.setString(7, sourcingForPLMBean.getMultilevelMaterialCategories());

            preparedStatement.setString(8, sourcingForPLMBean.getBrandId());
            preparedStatement.setString(9, sourcingForPLMBean.getChineseName());
            preparedStatement.setString(10, sourcingForPLMBean.getEnglishName());
            preparedStatement.setString(11, sourcingForPLMBean.getAbbreviation());
            preparedStatement.setString(12, sourcingForPLMBean.getBrandState());
            preparedStatement.setString(13, sourcingForPLMBean.getFirstGradeCategory());
            preparedStatement.setString(14, sourcingForPLMBean.getMultilevelMaterialCategories());

            preparedStatement.addBatch();
            preparedStatement.executeBatch();

        }

        System.out.println("插入数据结束");
        preparedStatement.close();
//        conn.close();
        conn.release();
    }

    //查询数据

    /**
     * 去除品牌状态为废弃的品牌
     *
     * @return
     */
    public static List<SourcingForPLMBean> queryAllBrand(String state) {
        WTConnection conn = null;
        List<SourcingForPLMBean> sourcingForPLMBeans = new ArrayList<>();
        PreparedStatement preparedStatement;
        StringBuilder sql = new StringBuilder();
        ResultSet resultSet = null;
        sql.append("SELECT x.*,x.ROWID FROM PLM.TM_SOURCINGFORPLM x WHERE BRANDSTATE <> ?");
        try {
        	MethodContext methodcontext = MethodContext.getContext();
            conn = (WTConnection) methodcontext.getConnection();
            preparedStatement = conn.prepareStatement(sql.toString());
            preparedStatement.setString(1, state);
            resultSet = preparedStatement.executeQuery();

            System.out.println("========SQL====" + sql.toString());

            while (resultSet.next()) {
                SourcingForPLMBean sourcingForPLMBean = new SourcingForPLMBean();
                sourcingForPLMBean.setBrandId(resultSet.getString("brandId"));
                sourcingForPLMBean.setChineseName(resultSet.getString("chineseName"));
                sourcingForPLMBean.setEnglishName(resultSet.getString("englishName"));
                sourcingForPLMBean.setAbbreviation(resultSet.getString("abbreviation"));
                sourcingForPLMBean.setBrandState(resultSet.getString("brandState"));
                sourcingForPLMBean.setFirstGradeCategory(resultSet.getString("firstGradeCategory"));
                sourcingForPLMBean.setMultilevelMaterialCategories(resultSet.getString("MultilevelMaterialCategories"));
                sourcingForPLMBean.setCreateTime(new Date(resultSet.getTimestamp("createTIme").getTime()));
                System.out.println("查询结果：" + sourcingForPLMBean.toString());
                sourcingForPLMBeans.add(sourcingForPLMBean);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
    	   if (conn != null) {
               conn.release();
           }
        }
        return sourcingForPLMBeans;
    }

    /**
     * 去除品牌状态为废弃的品牌
     *
     * @return
     */
    public static List<String> queryAllBrandNameByState(String state) {
        WTConnection conn = null;
        List<String> brandNames = new ArrayList<>();
        PreparedStatement preparedStatement;
        StringBuilder sql = new StringBuilder();
        ResultSet resultSet = null;
        sql.append("SELECT chineseName FROM PLM.TM_SOURCINGFORPLM x WHERE BRANDSTATE <> ?");
        try {
        	MethodContext methodcontext = MethodContext.getContext();
            conn = (WTConnection) methodcontext.getConnection();
            preparedStatement = conn.prepareStatement(sql.toString());
            preparedStatement.setString(1, state);
            resultSet = preparedStatement.executeQuery();

            System.out.println("========SQL====" + sql.toString());

            while (resultSet.next()) {
                String brandName = resultSet.getString("chineseName");
                if (!brandNames.contains(brandName)) {
                    brandNames.add(brandName);
                }
            }
            System.out.println("查询所有的品牌名称：" + brandNames);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
     	   if (conn != null) {
               conn.release();
           }
        }

        return brandNames;
    }


    /**
     * 获取所有的brandId并且去重
     *
     * @param sourcingForPLMBeanList
     * @return
     */
    private static List<String> getBrandIdList(List<SourcingForPLMBean> sourcingForPLMBeanList) {
        List<String> brandIdList = new ArrayList<>();
        for (SourcingForPLMBean sourcingForPLMBean : sourcingForPLMBeanList) {
            if (!brandIdList.contains(sourcingForPLMBean.getBrandId())) {
                brandIdList.add(sourcingForPLMBean.getBrandId());
            }
        }
        return brandIdList;
    }


    public static void main(String[] args) {
        queryAllBrand("废弃");
        queryAllBrandNameByState("废弃");
    }
}
